<?php

namespace app\common\library;

/**
 * excel模型 - 数据对象模型
 * Author martinsun <syh@sunyonghong.com>
 */

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\App;

class Excel
{

    /**
     * 导出excel
     * Author MartinSun<syh@sunyonghong.com>
     * Date：   2017-06-08
     *
     * @param string $xlsName 文件名
     * @param array  $xlsCell 列表数据配置
     * @param array  $xlsData 列表数据
     */
    public static function exportExcel($expTitle, $expCellName, $expTableData, $outinput = true, $dirConfig = [])
    {
        set_time_limit(0);
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle); //文件名称
        $fileName = date('YmdHis'); //or $xlsTitle 文件名称可根据自己情况设定
        $cellNum  = count($expCellName);
        $dataNum  = count($expTableData);

        // 内存配置
        //$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        //$cacheSettings = ['memoryCacheSize' => '1024MB'];
        //PHPExcel_Settings::setCacheStorageMethod($cacheMethod,$cacheSettings);
        //加载phpexcel
        $objPHPExcel = new Spreadsheet();
        $cellName    = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];
        // // $objPHPExcel->getProperties(0)->setCreator(getUserName($_SESSION['mid'])); //设置创建者
        $objPHPExcel->getProperties()->setTitle($expTitle); //设置标题
        $objActSheet = $objPHPExcel->getActiveSheet();
        $objActSheet->mergeCells('A1:' . $cellName[$cellNum - 1] . '1'); //合并单元格
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
        $objActSheet->getStyle('A')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
        $objActSheet->getStyle('B')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT); //左对齐
        $objActSheet->getStyle('2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
        $objActSheet->getStyle('2')->getFont()->setBold(true); //标题栏加粗
        $objActSheet->setCellValue('A1', '【' . $expTitle . '】导出时间:' . date('Y-m-d H:i:s'));
        for ($i = 0; $i < $cellNum; $i++) {
            $objActSheet->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }
        for ($i = 0; $i < $dataNum; $i++) {
            $data = $expTableData[$i];
            for ($j = 0; $j < $cellNum; $j++) {
                $objActSheet->setCellValue($cellName[$j] . ($i + 3), $data[$expCellName[$j][0]]);
            }
        }
        $objWriter = new Xlsx($objPHPExcel);
        if ($outinput === true) {
            // header('Accept-Ranges:bytes');
            // header('pragma:public');
            header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            // header('Content-type:application/vnd.ms-excel');
            header("Content-Disposition:attachment;filename=" . $fileName . '.xlsx'); //attachment新窗口打印inline本窗口打印
            header('Cache-Control: max-age=0'); //禁止缓存
            header('Access-Control-Allow-Origin:*');
            ob_clean();
            $objWriter->save("php://output");
            return;
        }

        $path     = isset($dirConfig['path']) ? $dirConfig['path'] : '';
        $filepath = self::mkExceldir('excel' . '/' . $path);
        $filepath = $filepath . '/' . iconv("UTF-8", "GB2312//IGNORE", $fileName) . '.xlsx';
        $objWriter->save($filepath);
        if (!file_exists($filepath)) {
            return false;
        }
        return iconv('UTF-8', 'GB2312//IGNORE', 'excel' . '/' . $path . '/' . $fileName) . '.xlsx';

    }

    /**
     * 导出excel
     * Author MartinSun<syh@sunyonghong.com>
     * Date：   2017-06-08
     *
     * @param string $xlsName 文件名
     * @param array  $xlsCell 列表数据配置
     * @param array  $xlsData 列表数据
     */
    public static function export($xlsName, $xlsCell, $xlsData, $outinput = true, $dirConfig = [])
    {
        return self::exportExcel($xlsName, $xlsCell, $xlsData, $outinput, $dirConfig);
    }

    /**
     * 创建excel导出保存目录
     * Author MartinSun<syh@sunyonghong.com>
     * Date：   2017-10-12
     *
     * @param string $path [description]
     * @return [type] [description]
     */
    public static function mkExceldir($path = '')
    {
        $rootPath = App::getRootPath() . 'public/storage/';
        $path     = $rootPath . DIRECTORY_SEPARATOR . $path;
        if (!is_dir($path)) {
            @mkdir($path, 0777, true);
            // @chmod($path, 0777);
        }
        return $path;
    }

    /**
     * 导入excel
     * Author MartinSun<syh@sunyonghong.com>
     * Date：   2017-10-12
     *
     * @param string  $file_path excel文件的路径
     * @param boolean $toArray   是否返回数组数据
     * @return [type] [description]
     */
    public static function import($file_path = '', $toArray = true)
    {
        $excel = IOFactory::load($file_path);
        if ($toArray === false) {
            return $excel;
        }
        $sheet = $excel->getActiveSheet(0);
        return $sheet->toArray();
    }
}
